package com.csii.modules.online.config.util;

import com.csii.core.util.oConvertUtils;
import com.csii.modules.online.cgform.entity.OnlCgformField;
import com.csii.modules.online.config.db.DataBaseConfig;
import com.csii.modules.online.config.db.FreemarkerConfig;
import com.csii.modules.online.config.exception.DBException;
import com.csii.modules.online.config.service.DbTableHandler;
import com.google.common.collect.Lists;
import freemarker.template.TemplateException;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.tool.schema.TargetType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class SqlFreemarkerUtil {
	private static final Logger LOGGER = LoggerFactory.getLogger(SqlFreemarkerUtil.class);
	private static final String TEMPLATE_FTL = "org/jeecg/modules/online/config/engine/tableTemplate.ftl";
	private static final List COLUMNFILTER=Arrays.asList("total_row_num","total_num","date_date");
	private static DbTableHandler handler;

	public SqlFreemarkerUtil() throws SQLException, DBException {
		handler = DialectUtil.getTableHandle();
	}

	public static void getCreateSql(FreemarkerConfig var0) throws IOException, TemplateException, HibernateException, SQLException, DBException {
		String var1 = DialectUtil.getDatabaseType();
		if ("ORACLE".equals(var1)) {
			ArrayList var2 = Lists.newArrayList();

			OnlCgformField var4;
			for (Iterator var3 = var0.getColumns().iterator(); var3.hasNext(); var2.add(var4)) {
				var4 = (OnlCgformField) var3.next();
				if ("int".equals(var4.getDbType())) {
					var4.setDbType("double");
					var4.setDbPointLength(0);
				}
			}

			var0.setColumns(var2);
		}

		String var14 = FreemarkerHelper.process("com/csii/modules/online/config/engine/tableTemplate.ftl", paramConfig(var0, var1));
		LOGGER.info(var14);
		HashMap var15 = new HashMap();
		DataBaseConfig var16 = var0.getDbConfig();
		var15.put("hibernate.connection.driver_class", var16.getDriverClassName());
		var15.put("hibernate.connection.url", var16.getUrl());
		var15.put("hibernate.connection.username", var16.getUsername());
		var15.put("hibernate.connection.password", var16.getPassword());
		var15.put("hibernate.show_sql", true);
		var15.put("hibernate.format_sql", true);
		var15.put("hibernate.temp.use_jdbc_metadata_defaults", false);
		var15.put("hibernate.dialect", DialectUtil.getDialect(var1));
		var15.put("hibernate.hbm2ddl.auto", "create");
		var15.put("hibernate.connection.autocommit", false);
		var15.put("hibernate.current_session_context_class", "thread");
		StandardServiceRegistry var5 = (new StandardServiceRegistryBuilder()).applySettings(var15).build();
		MetadataSources var6 = new MetadataSources(var5);
		ByteArrayInputStream var7 = new ByteArrayInputStream(var14.getBytes());
		var6.addInputStream(var7);
		Metadata var8 = var6.buildMetadata();
		SchemaExport var9 = new SchemaExport();
		var9.create(EnumSet.of(TargetType.DATABASE), var8);
		var7.close();
		List var10 = var9.getExceptions();
		Iterator var11 = var10.iterator();

		Exception var12;
		while (true) {
			if (!var11.hasNext()) {
				return;
			}

			var12 = (Exception) var11.next();
			if (var12 instanceof SQLSyntaxErrorException) {
				SQLSyntaxErrorException var13 = (SQLSyntaxErrorException) var12.getCause();
				if ("42000".equals(var13.getSQLState())) {
					continue;
				}
				break;
			} else {
				if (!"com.microsoft.sqlserver.jdbc.SQLServerException".equals(var12.getCause().getClass().getName())) {
					break;
				}

				if (var12.getCause().toString().indexOf("Incorrect syntax near the keyword") != -1) {
					var12.printStackTrace();
					throw new DBException(var12.getCause().getMessage());
				}

				LOGGER.error(var12.getMessage());
			}
		}

		throw new DBException(var12.getMessage());
	}

	public List<String> getUpdateSql(FreemarkerConfig config) throws DBException, SQLException {
		String databaseType = DialectUtil.getDatabaseType();
		String tableName = DialectUtil.getTableName(config.getTableName(), databaseType);
		String alterSql = "alter table  " + tableName + " ";
		List<String> list = new ArrayList();
		try {
			//当前表元数据
			Map<String,MetaDataUtil> columnMetaData = this.getColumnMetaData((String) null, tableName);
			//目标表元数据
			Map<String,MetaDataUtil> newColumnMetaData = this.metaDataMap(config);
			//新老表的字段映射
			Map<String,String> fieldMap = this.updateFieldMap(config.getColumns());
			//遍历新的元数据，执行更新操作
			for(String field: newColumnMetaData.keySet())
			{
				MetaDataUtil metaDataUtil;
				//老元数据不存在的字段，直接新增字段
				if (!columnMetaData.containsKey(field)) {
					metaDataUtil = (MetaDataUtil) newColumnMetaData.get(field);
					String var17 = (String) fieldMap.get(field);
					if (fieldMap.containsKey(field) && columnMetaData.containsKey(var17)) {
						MetaDataUtil var13 = (MetaDataUtil) columnMetaData.get(var17);
						String fieldName = handler.getReNameFieldName(metaDataUtil);
						if ("SQLSERVER".equals(databaseType)) {
							list.add(fieldName);
						} else {
							list.add(alterSql + fieldName);
						}

						String var15 = this.getCgformFieldUpdateSql(field, metaDataUtil.getColumnId());
						list.add(var15);
						if (!var13.equals(metaDataUtil)) {
							list.add(alterSql + this.getUpdateColumnSql(metaDataUtil, var13));
							if ("POSTGRESQL".equals(databaseType)) {
								list.add(alterSql + this.getSpecialHandle(metaDataUtil, var13));
							}
						}

						if (!"SQLSERVER".equals(databaseType) && !var13.compareCommentEq(metaDataUtil)) {
							list.add(this.getCommentSql(metaDataUtil));
						}
					} else {
						list.add(alterSql + this.getAddColumnSql(metaDataUtil));
						if (!"SQLSERVER".equals(databaseType) && StringUtils.isNotEmpty(metaDataUtil.getComment())) {
							list.add(this.getCommentSql(metaDataUtil));
						}
					}
				} else {
					//存在的字段更新字段信息
					metaDataUtil =  columnMetaData.get(field);
					MetaDataUtil var12 = newColumnMetaData.get(field);
					//更新字段信息
					if (!metaDataUtil.compare((Object) var12, databaseType)) {
						list.add(alterSql + this.getUpdateColumnSql(var12, metaDataUtil));
					}
					//更新注释信息
					if (!"SQLSERVER".equals(databaseType) && !"ORACLE".equals(databaseType) && !metaDataUtil.compareCommentEq(var12)) {
						list.add(this.getCommentSql(var12));
					}
				}

			}
			//遍历老的元数据查找不存在的，执行删除操作
			for (String field:columnMetaData.keySet()) {
				if (!newColumnMetaData.containsKey(field.toLowerCase()) && !fieldMap.containsValue(field.toLowerCase())) {
					list.add(alterSql + this.getDropColumnSql(field));
				}
			}
		} catch (SQLException e) {
			throw new RuntimeException();
		}
		LOGGER.info(" db update sql : " + list.toString());
		return list;
	}

	private static Map<String, Object> paramConfig(FreemarkerConfig config, String dataType) {
		HashMap com = new HashMap();
		for (OnlCgformField field:config.getColumns()) {
			field.setDbDefaultVal(parseValue(field.getDbDefaultVal()));
		}
		com.put("entity", config);
		com.put("dataType", dataType);
		return com;
	}

	private Map<String, MetaDataUtil> getColumnMetaData(String databaseName, String tableName) throws SQLException {
		HashMap hashMap = new HashMap();
		try(Connection connection = DialectUtil.getConnection()) {
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet resultSet = metaData.getColumns((String) null, databaseName, tableName, "%");
			while (resultSet.next()) {
				MetaDataUtil metaDataUtil = new MetaDataUtil();
				metaDataUtil.setTableName(tableName);
				String columnName= resultSet.getString("COLUMN_NAME").toLowerCase();
				if(COLUMNFILTER.contains(columnName)){
					LOGGER.info("skip column::"+columnName);
					continue;
				}
				metaDataUtil.setColumnName(columnName);
				String typeName = resultSet.getString("TYPE_NAME");
				int decimalDigits = resultSet.getInt("DECIMAL_DIGITS");
				String dataType = handler.getMatchClassTypeByDataType(typeName, decimalDigits);
				metaDataUtil.setColunmType(dataType);
				metaDataUtil.setColumnSize(resultSet.getInt("COLUMN_SIZE"));
				metaDataUtil.setDecimalDigits(decimalDigits);
				metaDataUtil.setIsNullable(resultSet.getInt("NULLABLE") == 1 ? "Y" : "N");
				metaDataUtil.setComment(resultSet.getString("REMARKS"));
				String columnDef = resultSet.getString("COLUMN_DEF");
				metaDataUtil.setFieldDefault(parseValue(columnDef) == null ? "" : parseValue(columnDef));
				LOGGER.info("getColumnMetadataFormDataBase --->COLUMN_NAME:" + columnName.toUpperCase() + " TYPE_NAME :" + typeName + " DECIMAL_DIGITS:" + decimalDigits + " COLUMN_SIZE:" + metaDataUtil.getColumnSize());
				hashMap.put(columnName, metaDataUtil);
			}
		} catch (Exception var17) {
			LOGGER.error(var17.getMessage(), var17);
		}
		return hashMap;
	}

	private Map<String, MetaDataUtil> metaDataMap(FreemarkerConfig config) {
		HashMap map = new HashMap();
		for(OnlCgformField field : config.getColumns()){
			MetaDataUtil metaDataUtil = new MetaDataUtil();
			metaDataUtil.setTableName(config.getTableName().toLowerCase());
			metaDataUtil.setColumnId(field.getId());
			metaDataUtil.setColumnName(field.getDbFieldName().toLowerCase());
			metaDataUtil.setColumnSize(field.getDbLength());
			metaDataUtil.setColunmType(field.getDbType().toLowerCase());
			metaDataUtil.setIsNullable(field.getDbIsNull() == 1 ? "Y" : "N");
			metaDataUtil.setComment(field.getDbFieldTxt());
			metaDataUtil.setDecimalDigits(field.getDbPointLength());
			metaDataUtil.setFieldDefault(parseValue(field.getDbDefaultVal()));
			metaDataUtil.setPkType(config.getJformPkType() == null ? "UUID" : config.getJformPkType());
			metaDataUtil.setOldColumnName(field.getDbFieldNameOld() != null ? field.getDbFieldNameOld().toLowerCase() : null);
			LOGGER.info("getColumnMetadataFormCgForm ----> DbFieldName: " + field.getDbFieldName().toLowerCase() + " | DbType: " + field.getDbType().toLowerCase() + " | DbPointLength:" + field.getDbPointLength() + " | DbLength:" + field.getDbLength());
			map.put(field.getDbFieldName().toLowerCase(), metaDataUtil);
		}

		return map;
	}

	private Map<String, String> updateFieldMap(List<OnlCgformField> var1) {
		HashMap var2 = new HashMap();
		Iterator var3 = var1.iterator();

		while (var3.hasNext()) {
			OnlCgformField var4 = (OnlCgformField) var3.next();
			var2.put(var4.getDbFieldName(), var4.getDbFieldNameOld());
		}

		return var2;
	}

	private String getDropColumnSql(String var1) {
		return handler.getDropColumnSql(var1);
	}

	private String getUpdateColumnSql(MetaDataUtil var1, MetaDataUtil var2) throws DBException {
		return handler.getUpdateColumnSql(var1, var2);
	}

	private String getSpecialHandle(MetaDataUtil var1, MetaDataUtil var2) {
		return handler.getSpecialHandle(var1, var2);
	}

	private String getReNameFieldName(MetaDataUtil var1) {
		return handler.getReNameFieldName(var1);
	}

	private String getAddColumnSql(MetaDataUtil var1) {
		return handler.getAddColumnSql(var1);
	}

	private String getCommentSql(MetaDataUtil var1) {
		return handler.getCommentSql(var1);
	}

	private String getCgformFieldUpdateSql(String var1, String var2) {
		return "update onl_cgform_field set DB_FIELD_NAME_OLD = '" + var1 + "' where ID ='" + var2 + "'";
	}

	private int executeFieldUpdate(String oldFieldName, String newFieldName, Session session) {
		return session.createSQLQuery("update onl_cgform_field set DB_FIELD_NAME_OLD= '" + oldFieldName + "' where ID ='" + newFieldName + "'").executeUpdate();
	}

	private static String parseValue(String val) {
		if (StringUtils.isNotEmpty(val)) {
			try {
				Double.valueOf(val);
			} catch (Exception var2) {
				if (!val.startsWith("'") || !val.endsWith("'")) {
					val = "'" + val + "'";
				}
			}
		}
		return val;
	}

	public String dropIndexs(String index, String table) {
		return handler.dropIndexs(index, table);
	}

	public String countIndex(String index, String table) {
		return handler.countIndex(index, table);
	}

	public static List<String> getIndexList(String tableName) throws SQLException {
		ResultSet resultSet = null;
		ArrayList arrayList = new ArrayList();
		try(Connection connection = DialectUtil.getConnection()) {
			DatabaseMetaData var4 = connection.getMetaData();
			resultSet = var4.getIndexInfo((String) null, (String) null, tableName, false, false);
			ResultSetMetaData var5 = resultSet.getMetaData();

			while (resultSet.next()) {
				String indexName = resultSet.getString("INDEX_NAME");
				if (oConvertUtils.isEmpty(indexName)) {
					indexName = resultSet.getString("index_name");
				}

				if (oConvertUtils.isNotEmpty(indexName)) {
					arrayList.add(indexName);
				}
			}
		} catch (SQLException var10) {
			LOGGER.error(var10.getMessage(), var10);
		} finally {
			if(resultSet!=null)
				resultSet.close();
		}

		return arrayList;
	}
}
